﻿/*  ----------------------------------------------------------------------------
 *  Author:
 *  Chandra Shekhar Tripathi
 *  ----------------------------------------------------------------------------
 *  Product Name:
 *  DbDocumenter
 *  ----------------------------------------------------------------------------
 *  Year:
 *  2009
 *  ----------------------------------------------------------------------------
 *  File:
 *  SQLDBInfo.cs
 *  ----------------------------------------------------------------------------
 */
namespace DBDocumenter.DAL.SQL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;

    using DBDocumenter.BLL.Entities;
    using DBDocumenter.Exception;

    using Microsoft.Practices.EnterpriseLibrary.Data;
    using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

    public class SQLDBInfo
    {
        #region Fields

        private static SQLDBInfo instance = new SQLDBInfo();

        #endregion Fields

        #region Constructors

        /// <summary>
        /// Initializes a new instance of the <see cref="SQLDBInfo"/> class.
        /// </summary>
        private SQLDBInfo()
        {
        }

        #endregion Constructors

        #region Properties

        /// <summary>
        /// Gets or sets the instance.
        /// </summary>
        /// <value>The instance.</value>
        public static SQLDBInfo Instance
        {
            get { return SQLDBInfo.instance; }
            set { SQLDBInfo.instance = value; }
        }

        #endregion Properties

        #region Methods

        /// <summary>
        /// Gets the columns.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <returns></returns>
        public List<Column> GetColumns(string connectionString, string tableName)
        {
            try
            {
                List<Column> columns = new List<Column>();
                Database myDatabase = new SqlDatabase(connectionString);
                string sqlCommand = DBConstants.GetColumnSqlCommand(tableName);

                using (DbCommand command = myDatabase.GetSqlStringCommand(sqlCommand))
                {
                    using (IDataReader reader = myDatabase.ExecuteReader(command))
                    {
                        while (reader.Read())
                        {
                            Column column = new Column();
                            column.Name = reader["COLUMN_NAME"].ToString();
                            column.DataType = reader["DATA_TYPE"].ToString();
                            column.IsNullable = (reader["IS_NULLABLE"].ToString().Equals("YES")) ? true : false;
                            column.Key = reader["COLUMN_KEYTYPE"].ToString();
                            column.OrdinalPosition = reader["ORDINAL_POSITION"].ToString();

                            columns.Add(column);
                            //databases.Add(reader["DATABASE_NAME"].ToString());
                        }
                    }
                }
                return columns;
            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogType.DataLayer, LogLevel.Error, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// Gets the databases.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <returns></returns>
        public List<ServerInfo> GetDatabases(string connectionString)
        {
            try
            {
                List<ServerInfo> servers = new List<ServerInfo>();
                Database myDatabase = new SqlDatabase(connectionString);
                string sqlCommand = DBConstants.GetDatabaseSqlCommand;
                using (DbCommand command = myDatabase.GetSqlStringCommand(sqlCommand))
                {
                    using (IDataReader reader = myDatabase.ExecuteReader(command))
                    {
                        while(reader.Read())
                        {
                            ServerInfo server = new ServerInfo();
                            server.DbName = reader["DATABASE_NAME"].ToString();
                            server.DbID = reader["DATABASE_ID"].ToString();//
                            server.DbCreateDate = reader["DATABASE_CREATEDATE"].ToString();

                            servers.Add(server);
                        }
                    }
                }
                return servers;
            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogType.DataLayer, LogLevel.Error, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// Gets the functions.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <returns></returns>
        public List<Function> GetFunctions(string connectionString)
        {
            try
            {
                List<Function> functions = new List<Function>();
                Database myDatabase = new SqlDatabase(connectionString);
                string sqlCommand = DBConstants.GetFunctionsSqlCommand;

                using (DbCommand command = myDatabase.GetSqlStringCommand(sqlCommand))
                {
                    using (IDataReader reader = myDatabase.ExecuteReader(command))
                    {
                        while (reader.Read())
                        {
                            Function function = new Function();

                            function.Name = reader["ROUTINE_NAME"].ToString();
                            function.Schema = reader["ROUTINE_SCHEMA"].ToString();
                            function.CreatedDate = reader["CREATED"].ToString();
                            function.LastAlteredDate = reader["LAST_ALTERED"].ToString();
                            function.Description = reader["ROUTINE_DEFINITION"].ToString();
                            function.Parameters = this.GetParameters(connectionString, reader["ROUTINE_NAME"].ToString());

                            functions.Add(function);
                            //databases.Add(reader["DATABASE_NAME"].ToString());
                        }
                    }
                }
                return functions;
            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogType.DataLayer, LogLevel.Error, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// Gets the parameters.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="spName">Name of the sp.</param>
        /// <returns></returns>
        public List<Parameter> GetParameters(string connectionString, string spName)
        {
            try
            {
                List<Parameter> parameters = new List<Parameter>();
                Database myDatabase = new SqlDatabase(connectionString);
                string sqlCommand = DBConstants.GetParameterSqlCommand(spName);

                using (DbCommand command = myDatabase.GetSqlStringCommand(sqlCommand))
                {
                    using (IDataReader reader = myDatabase.ExecuteReader(command))
                    {
                        while (reader.Read())
                        {
                            Parameter parameter = new Parameter();

                            parameter.OrdinalPosition = reader["ORDINAL_POSITION"].ToString();
                            parameter.Name = reader["PARAMETER_NAME"].ToString();
                            parameter.DataType = reader["DATA_TYPE"].ToString();
                            parameter.Mode = reader["PARAMETER_MODE"].ToString();

                            parameters.Add(parameter);
                            //databases.Add(reader["DATABASE_NAME"].ToString());
                        }
                    }
                }
                return parameters;
            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogType.DataLayer, LogLevel.Error, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// Gets the stored procedures.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <returns></returns>
        public List<StoredProcedure> GetStoredProcedures(string connectionString)
        {
            try
            {
                List<StoredProcedure> storedProcedures = new List<StoredProcedure>();
                Database myDatabase = new SqlDatabase(connectionString);
                string sqlCommand = DBConstants.GetStoredProcedureSqlCommand;

                using (DbCommand command = myDatabase.GetSqlStringCommand(sqlCommand))
                {
                    using (IDataReader reader = myDatabase.ExecuteReader(command))
                    {
                        while (reader.Read())
                        {
                            StoredProcedure storedProcedure = new StoredProcedure();

                            storedProcedure.Name = reader["ROUTINE_NAME"].ToString();
                            storedProcedure.Schema = reader["ROUTINE_SCHEMA"].ToString();
                            storedProcedure.CreatedDate = reader["CREATED"].ToString();
                            storedProcedure.LastAlteredDate = reader["LAST_ALTERED"].ToString();
                            storedProcedure.Description = reader["ROUTINE_DEFINITION"].ToString();
                            storedProcedure.Parameters = this.GetParameters(connectionString, reader["ROUTINE_NAME"].ToString());

                            storedProcedures.Add(storedProcedure);
                            //databases.Add(reader["DATABASE_NAME"].ToString());
                        }
                    }
                }
                return storedProcedures;
            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogType.DataLayer, LogLevel.Error, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// Gets the tables.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <returns></returns>
        public List<Table> GetTables(string connectionString)
        {
            try
            {
                List<Table> tables = new List<Table>();
                Database myDatabase = new SqlDatabase(connectionString);
                string sqlCommand = DBConstants.GetTablesSqlCommand;
                using (DbCommand command = myDatabase.GetSqlStringCommand(sqlCommand))
                {
                    using (IDataReader reader = myDatabase.ExecuteReader(command))
                    {
                        while (reader.Read())
                        {
                            Table table = new Table();
                            table.Name = reader["TABLE_NAME"].ToString();
                            table.Schema = reader["TABLE_SCHEMA"].ToString();
                            table.Type = reader["TABLE_TYPE"].ToString();
                            table.Colunms = this.GetColumns(connectionString, table.Name);
                            tables.Add(table);
                            //databases.Add(reader["DATABASE_NAME"].ToString());
                        }
                    }
                    return tables;
                }

            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogType.DataLayer, LogLevel.Error, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// Gets the views.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <returns></returns>
        public List<View> GetViews(string connectionString)
        {
            try
            {
                List<View> views = new List<View>();
                Database myDatabase = new SqlDatabase(connectionString);
                string sqlCommand = DBConstants.GetViewsSqlCommand;

                using (DbCommand command = myDatabase.GetSqlStringCommand(sqlCommand))
                {
                    using (IDataReader reader = myDatabase.ExecuteReader(command))
                    {
                        while (reader.Read())
                        {
                            View view = new View();

                            view.Name = reader["TABLE_NAME"].ToString();
                            view.Schema = reader["TABLE_SCHEMA"].ToString();
                            view.CheckSchema = reader["CHECK_OPTION"].ToString();
                            view.IsUpdateble = reader["IS_UPDATABLE"].ToString();
                            view.Description = reader["VIEW_DEFINITION"].ToString();
                            view.Colunms = this.GetColumns(connectionString, reader["TABLE_NAME"].ToString());

                            views.Add(view);
                            //databases.Add(reader["DATABASE_NAME"].ToString());
                        }
                    }
                }
                return views;
            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogType.DataLayer, LogLevel.Error, ex.ToString());
                return null;
            }
        }

        #endregion Methods
    }
}